require('js/lib/require_patch').monkeypatch(this); // Needed for Android

exports.Db = function() {
	this.db = Titanium.Database.install('starter.db','localDB');
	this.db.close();
	return this;
};

// Test Categories are currently READ-ONLY
exports.Db.prototype.listTestCategories = function(filter, filterParams) {
	var db = Ti.Database.open('localDB');

	var Category = require('js/model/TestCategory');
	var list = [];
	
	var sql = 'SELECT * FROM test_category';
	if (filter)
		sql += ' WHERE ' + filter;

	Ti.API.debug('SQL is: '+sql+' Params are: '+filterParams);
	var rows = db.execute(sql,filterParams ? filterParams : []);
	while (rows.isValidRow()) {
		list.push(new Category({
			id: rows.fieldByName('id'),
			name: rows.fieldByName('name')
		}));
		rows.next();
	}
	rows.close();
	db.close();
	
	Ti.API.debug(' +++ returning ' + list.length + ' Categories');
	
	return list;
}

// Tests are currently READ-ONLY
exports.Db.prototype.listTests = function(filter, filterParams) {
	var db = Ti.Database.open('localDB');

	var Test = require('js/model/Test');
	var list = [];
	
	var sql = 'SELECT * FROM tests';
	if (filter)
		sql += ' WHERE ' + filter;
	else
		sql += ' WHERE "primary" = 1';

	Ti.API.debug('SQL is: '+sql+' using parameters: '+filterParams);
	var rows = db.execute(sql, filterParams||[]);
	while (rows.isValidRow()) {
		list.push(new Test({
			id: rows.fieldByName('id'),
			name: rows.fieldByName('name'),
			binary: rows.fieldByName('binary'),
			category: rows.fieldByName('category'),
			primary: rows.fieldByName('primary'),
			fn_test: rows.fieldByName('fn_test'),
			dn_test: rows.fieldByName('dn_test'),
			fp_test: rows.fieldByName('fp_test'),
			dp_test: rows.fieldByName('dp_test'),
			condition: rows.fieldByName('condition')
		}));
		rows.next();
	}
	rows.close();
	db.close();
	
	Ti.API.debug(' +++ returning ' + list.length + ' Tests');
	
	return list;
}


// Patient Tests 
exports.Db.prototype.listPatientTests = function(filter, filterParams) {
	var db = Ti.Database.open('localDB');

	var PatientTest = require('js/model/PatientTest');
	var Test = require('js/model/Test');
	var list = [];
	
	var sql = 'SELECT *, t.id AS tid, p.id AS pid FROM patient_tests p JOIN tests t ON (t.id = test_id)';
	if (filter)
		sql += ' WHERE ' + filter;

	Ti.API.debug('SQL is: '+sql);
	var rows = db.execute(sql, filterParams);
	while (rows.isValidRow()) {
		var test = new Test({
			id: rows.fieldByName('tid'),
			name: rows.fieldByName('name'),
			binary: rows.fieldByName('binary'),
			category: rows.fieldByName('category'),
			primary: rows.fieldByName('primary'),
			fn_test: rows.fieldByName('fn_test'),
			dn_test: rows.fieldByName('dn_test'),
			fp_test: rows.fieldByName('fp_test'),
			dp_test: rows.fieldByName('dp_test'),
			condition: rows.fieldByName('condition')
		});
		
		list.push(new PatientTest({
			id: rows.fieldByName('pid'),
			patient_id: rows.fieldByName('patient_id'),
			provider_id: rows.fieldByName('provider_id'),
			test_id: rows.fieldByName('test_id'),
			test_date: rows.fieldByName('test_date'),
			appointment_id: rows.fieldByName('appointment_id'),
			result: rows.fieldByName('result'),
			test: test
		}));
		rows.next();
	}
	rows.close();
	db.close();
	
	Ti.API.debug(' +++ returning ' + list.length + ' PatientTests');
	
	return list;
}

exports.Db.prototype.addPatientTests = function(test) {
	var db = Ti.Database.open('localDB');
	
	var sql = 'INSERT INTO patient_tests (patient_id, provider_id, test_id, result, test_date, appointment_id) VALUES(?,?,?,?,?,?)';

	db.execute(sql, test.patientId, test.providerId, test.testId, test.result, test.testDate, test.appointmentId);

	db.close();
}

// Test results
exports.Db.prototype.listPatientTestResults = function(patientId, testId) {
	var db = Ti.Database.open('localDB');

	var test = {};
	var Test = require('js/model/Test');
	var PatientTest = require('js/model/PatientTest');
	
	var sql = 'SELECT *, t.id as tid, p.id as pid FROM tests t LEFT JOIN patient_tests p on (t.id = p.test_id and p.patient_id = ?) WHERE t.id = ? ORDER BY test_date DESC LIMIT 1';

	Ti.API.debug('SQL is: '+sql+' parameters are: '+[patientId, testId]);
	var rows = db.execute(sql, [patientId, testId]);
	if (rows.isValidRow()) {
		var patientTest = new PatientTest({
			id: rows.fieldByName('pid'),
			patient_id: rows.fieldByName('patient_id'),
			provider_id: rows.fieldByName('provider_id'),
			test_id: testId,
			test_date: rows.fieldByName('test_date'),
			appointment_id: rows.fieldByName('appointment_id'),
			result: rows.fieldByName('result')
		});
		
		test = new Test({
			id: rows.fieldByName('tid'),
			name: rows.fieldByName('name'),
			binary: rows.fieldByName('binary'),
			category: rows.fieldByName('category'),
			primary: rows.fieldByName('primary'),
			fn_test: rows.fieldByName('fn_test'),
			dn_test: rows.fieldByName('dn_test'),
			fp_test: rows.fieldByName('fp_test'),
			dp_test: rows.fieldByName('dp_test'),
			condition: rows.fieldByName('condition'),
			result: patientTest
		});
	}
	rows.close();
	db.close();
	
	Ti.API.debug(' +++ returning ' + (test ? 1 : 0) + ' Patient Test Resultss');
		
	return test;
}
// Hmm, works on Android and FF plug-in, but not on iOs.  What gives?
exports.Db.prototype.listPatientTestResultsForResult = function(patientId, result) {
	var db = Ti.Database.open('localDB');

	var list = [];
	var Test = require('js/model/Test');
	var Category  = require('js/model/TestCategory');
	var PatientTest = require('js/model/PatientTest');
	
	var sql = 'SELECT *, p.id as pid, t.id as tid, c.id as cid, t.name as tname, c.name as cname FROM tests t join patient_tests p on (test_id = t.id AND patient_id = ?) join test_category c on (c.id = category) GROUP BY t.id HAVING result = ? AND max(test_date)';

	Ti.API.debug('SQL is: '+sql+' parameters are: '+[patientId, result]);
	var rows = db.execute(sql, [patientId, result]);
	while (rows.isValidRow()) {
		var patientTest = new PatientTest({
			id: rows.fieldByName('pid'),
			patient_id: rows.fieldByName('patient_id'),
			provider_id: rows.fieldByName('provider_id'),
			test_id: rows.fieldByName('tid'),
			test_date: rows.fieldByName('test_date'),
			appointment_id: rows.fieldByName('appointment_id'),
			result: rows.fieldByName('result')
		});
		
		var category = new Category({
			id: rows.fieldByName('cid'),
			name: rows.fieldByName('cname')
		});
		
		test = new Test({
			id: rows.fieldByName('tid'),
			name: rows.fieldByName('tname'),
			binary: rows.fieldByName('binary'),
			category: rows.fieldByName('category'),
			primary: rows.fieldByName('primary'),
			fn_test: rows.fieldByName('fn_test'),
			dn_test: rows.fieldByName('dn_test'),
			fp_test: rows.fieldByName('fp_test'),
			dp_test: rows.fieldByName('dp_test'),
			condition: rows.fieldByName('condition'),
			result: patientTest,
			category: category
		});
		list.push(test);
		
		rows.next();
	}
	rows.close();
	db.close();
	
	Ti.API.debug(' +++ returning ' + list.length + ' Patient Test Results');
		
	return list;
}

// Providers are READ-ONLY
exports.Db.prototype.listProviders = function(filter, filterParams) {
	var db = Ti.Database.open('localDB');
	
	var Provider = require('js/model/Provider');
	var list = [];
	
	var sql = 'SELECT * FROM providers';
	if (filter) {
		sql += ' WHERE ' + filter;
	}
	
	Ti.API.debug('SQL is: '+sql+' using parameters: '+filterParams);
	var rows = db.execute(sql,filterParams||[]);
	while (rows.isValidRow()) {
		list.push(new Provider({
			id: rows.fieldByName('id'),
			first_name: rows.fieldByName('first_name'),
			last_name: rows.fieldByName('last_name'),
			prefix: rows.fieldByName('title')
		}));
		rows.next();
	}
	rows.close();
	db.close();
	
	Ti.API.debug(' +++ returning ' + list.length + ' Providers');
	
	return list;
}

// Patients
exports.Db.prototype.listPatients = function(filter) {
	var db = Ti.Database.open('localDB');

	var Patient = require('js/model/Patient');
	var list = [];
	
	var sql = 'SELECT * FROM patients';
	if (filter) {
		sql += ' WHERE ' + filter;
	}
	Ti.API.debug('SQL is: '+sql);
	var rows = db.execute(sql);
	while (rows.isValidRow()) {
		list.push(new Patient({
			id: rows.fieldByName('id'),
			first_name: rows.fieldByName('first_name'),
			last_name: rows.fieldByName('last_name'),
			e_mail: rows.fieldByName('e_mail'),
			birth_date: rows.fieldByName('birth_date')
		}));
		rows.next();
	}
	rows.close();
	db.close();
	
	Ti.API.debug(' +++ returning ' + list.length + ' Patients');
	
	return list;
}

exports.Db.prototype.addPatient = function(patient) {
	var db = Ti.Database.open('localDB');

	db.execute(
		'INSERT INTO patients(first_name,last_name,e_mail,birth_date) VALUES(?,?,?,?)',
		patient.firstName,
		patient.lastName,
		patient.eMail,
		patient.birthDate
	);
	
	db.close();

	//Dispatch a message to let others know the database has been updated
	Ti.App.fireEvent("app:databaseUpdated", { patient:patient } );

}

// NoteCategory are read-only
exports.Db.prototype.listNoteCategories = function(filter, filterParams) {
	var db = Ti.Database.open('localDB');

	var NoteCategory = require('js/model/NoteCategory');
	var list = [];
	
	var sql = 'SELECT * FROM note_category';
	if(filter) {
		sql += ' WHERE ' + filter;
	}
	sql + ' ORDER BY sequence';
	Ti.API.debug('SQL is: '+sql+ ' params are: '+filterParams);
	var rows = db.execute(sql, filterParams ? filterParams : []);
	while(rows.isValidRow()) {
		list.push(new NoteCategory({
			id : rows.fieldByName('id'),
			name : rows.fieldByName('name'),
			sequence : rows.fieldByName('sequence')
		}));
		rows.next();
	}
	rows.close();
	db.close();

	Ti.API.debug(' +++ returning ' + list.length + ' Note Categories');

	return list;
}

// Patient Appointments
exports.Db.prototype.listPatientAppointments = function(filter, filterParams) {
	var db = Ti.Database.open('localDB');

	var PatientAppointment = require('js/model/PatientAppointment');
	var list = [];
	
	var sql = 'SELECT * FROM patient_appointment';
	if(filter) {
		sql += ' WHERE ' + filter;
	}
	sql += ' ORDER BY date DESC';
	Ti.API.debug('SQL is: '+sql+ ' params are: '+filterParams);
	var rows = db.execute(sql, filterParams ? filterParams : []);
	while(rows.isValidRow()) {
		list.push(new PatientAppointment({
			id : rows.fieldByName('id'),
			date : rows.fieldByName('date'),
			patient_id : rows.fieldByName('patient_id'),
			type : rows.fieldByName('type')
		}));
		rows.next();
	}
	rows.close();
	db.close();

	Ti.API.debug(' +++ returning ' + list.length + ' Patient Appointments');

	return list;
}
exports.Db.prototype.addPatientAppointment = function(appointment) {
	var db = Ti.Database.open('localDB');

	globals.util.debugDump('addPatientAppointment appointment', appointment);
	db.execute(
		'INSERT INTO patient_appointment(date,patient_id,type) VALUES(?,?,?)',
		appointment.dateTime,
		appointment.patientId,
		appointment.type
	);
	
	db.close();

	//Dispatch a message to let others know the database has been updated
	Ti.App.fireEvent("app:databaseUpdated", { appointment:appointment } );
}
exports.Db.prototype.updatePatientAppointment = function(appointment) {
	var db = Ti.Database.open('localDB');

	db.execute(
		'UPDATE patient_appointment SET date = ?, patient_id = ?, type = ? WHERE id = ?',
		appointment.date,
		appointment.patientId,
		appointment.type,
		appointment.id
	);
	
	db.close();

	//Dispatch a message to let others know the database has been updated
	Ti.App.fireEvent("app:databaseUpdated", { appointment:appointment } );
}

// Patient Notes
exports.Db.prototype.listPatientNotes = function(patientId, filter, filterParams) {
	var db = Ti.Database.open('localDB');

	var PatientNote = require('js/model/PatientNote');
	var list = [];
	var params = new Array().concat(patientId,filterParams);
	
	var sql = 'SELECT * FROM patient_note WHERE patient_id = ?';
	if(filter) {
		sql += ' AND ' + filter;
	}
	sql += ' ORDER BY note_type';
	Ti.API.debug('SQL is: '+sql+ ' params are: '+params+ ' filter params are: '+filterParams);
	var rows = db.execute(sql, params);
	while(rows.isValidRow()) {
		list.push(new PatientNote({
			id : rows.fieldByName('id'),
			patient_id : rows.fieldByName('patient_id'),
			provider_id : rows.fieldByName('provider_id'),
			appointment_id : rows.fieldByName('appointment_id'),
			note_type : rows.fieldByName('note_type'),
			note_text : rows.fieldByName('note_text')
		}));
		rows.next();
	}
	rows.close();
	db.close();

	Ti.API.debug(' +++ returning ' + list.length + ' Patient Note(s)');

	return list;

}
exports.Db.prototype.addPatientNote = function(note) {
	var db = Ti.Database.open('localDB');

	globals.util.debugDump('addPatientNote note', note);
	db.execute(
		'INSERT INTO patient_note(patient_id,provider_id,appointment_id,note_type,note_text) VALUES(?,?,?,?,?)',
		note.patientId,
		note.providerId,
		note.appointmentId,
		note.type,
		note.text
	);
	
	db.close();

	//Dispatch a message to let others know the database has been updated
	Ti.App.fireEvent("app:databaseUpdated", { note:note } );
}
exports.Db.prototype.updatePatientNote = function(note) {
	var db = Ti.Database.open('localDB');

	db.execute(
		'UPDATE patient_note SET patient_id = ?, provider_id = ?, appointment_id = ?, note_type = ?, note_text = ? WHERE id = ?',
		note.patientId,
		note.providerId,
		note.appointmentId,
		note.type,
		note.text,
		note.id
	);
	
	db.close();

	//Dispatch a message to let others know the database has been updated
	Ti.App.fireEvent("app:databaseUpdated", { note:note } );
}

function timeStamp() {
	var db = Ti.Database.open('localDB');

	var rows = db.execute("SELECT datetime('now', 'localtime')");
	var timeStr = '';
	if (rows.isValidRow()) {
		timeStr = rows.field(0);
	}
	rows.close();
	db.close();
	
	return timeStr;
}

exports.Db.prototype.timeStamp = timeStamp;

// Treatments are READ-ONLY
exports.Db.prototype.listTreatments = function(filter, filterParams) {
	var db = Ti.Database.open('localDB');
	
	var Treatment = require('js/model/Treatment');
	var list = [];
	var sql = 'SELECT * FROM treatments';
	if(filter) {
		sql += ' WHERE ' + filter;
	}
	
	var rows = db.execute(sql, filterParams);
	while (rows.isValidRow()) {
		list.push(new Treatment({
			id: rows.fieldByName('id'),
			text: rows.fieldByName('text'),
			background_color: rows.fieldByName('background_color')
		}));
		rows.next();
	}
	rows.close();
	db.close();
	
	Ti.API.debug(' +++ returning ' + list.length + ' Treatments');
	
	return list;
}

// SelectionItems are READ-ONLY
exports.Db.prototype.listPicklistItems = function(filter, filterParams) {
	var db = Ti.Database.open('localDB');
	
	var SelectionItem = require('js/model/SelectionItem');
	var list = [];
	var sql = 'SELECT * FROM pick_lists';
	if(filter) {
		sql += ' WHERE ' + filter;
	}
	
	Ti.API.debug('SQL is: '+sql+ ' filter params are: '+filterParams);
	var rows = db.execute(sql, filterParams||[]);
	while (rows.isValidRow()) {
		list.push(new SelectionItem({
			id: rows.fieldByName('id'),
			name: rows.fieldByName('name'),
			description: rows.fieldByName('description'),
			type: rows.fieldByName('type'),
			group: rows.fieldByName('group')
		}));
		rows.next();
	}
	rows.close();
	db.close();
	
	Ti.API.debug(' +++ returning ' + list.length + ' List Items');
	
	return list;
}

// PatientTreatments
exports.Db.prototype.listPatientTreatments = function(filter, filterParams) {
	var db = Ti.Database.open('localDB');
	
	var PatientTreatment = require('js/model/PatientTreatment');
	var list = [];
	var sql = 'SELECT * FROM patient_treatments';
	if(filter) {
		sql += ' WHERE ' + filter;
	}
	
	Ti.API.debug('SQL is: '+sql+ ' params are: '+params+ ' filter params are: '+filterParams);
	var rows = db.execute(sql, filterParams||[]);
	while (rows.isValidRow()) {
		list.push(new PatientTreatment({
			id: rows.fieldByName('id'),
			provider_id: rows.fieldByName('provider_id'),
			patient_id: rows.fieldByName('patient_id'),
			appointment_id: rows.fieldByName('appointment_id'),
			treatment_id: rows.fieldByName('treatment_id'),
			treatment_selection_id: rows.fieldByName('treatment_selection_id'),
			notes: rows.fieldByName('notes')
		}));
		rows.next();
	}
	rows.close();
	db.close();
	
	Ti.API.debug(' +++ returning ' + list.length + ' PatientTreatments');
	
	return list;
}
exports.Db.prototype.addPatientTreatment = function(treatment) {
	var db = Ti.Database.open('localDB');

	globals.util.debugDump('addPatientTreatment treatment', treatment);
	db.execute(
		'INSERT INTO patient_treatments(patient_id,provider_id,appointment_id,treatment_id,treatment_selection_id,notes) VALUES(?,?,?,?,?,?)',
		treatment.patientId,
		treatment.providerId,
		treatment.appointmentId,
		treatment.treatmentId,
		treatment.treatmentSelectionId,
		treatment.notes
	);
	
	db.close();

	//Dispatch a message to let others know the database has been updated
	Ti.App.fireEvent("app:databaseUpdated", { treatment:treatment } );
}
exports.Db.prototype.updatePatientTreatment = function(treatment) {
	var db = Ti.Database.open('localDB');

	db.execute(
		'UPDATE patient_treatments SET patient_id = ?, provider_id = ?, appointment_id = ?, ,treatment_id = ?,treatment_selection_id = ?, notes = ? WHERE id = ?',
		treatment.patientId,
		treatment.providerId,
		treatment.appointmentId,
		treatment.treatmentId,
		treatment.treatmentSelectionId,
		treatment.notes,
		treatment.id
	);
	
	db.close();

	//Dispatch a message to let others know the database has been updated
	Ti.App.fireEvent("app:databaseUpdated", { treatment:treatment } );
}

/*
 * Not completed or tested.  The API should take model name(s), build a select statement, 
 * and retrieve the data, stuffing it into the appropriate model structure
 */
exports.Db.prototype.list = function(stmt, filter) {
	var db = Ti.Database.open('localDB');

	var list = [];
	
	var models = models(), result = [];
	// load return objects
	for (var tokens in stmt.split(' ')) {
		var model = models[token];
		if (model)
			result.push(model);
	}
	
	var rows = db.execute(stmt);
	while (rows.isValidRow()) {
		var i = 0;
		for (var model in result) {
			
		}
	}
	rows.close();
	db.close();
	
	return list;
	
};

camelize = function(str) {
	return str.replace(/[-_]([ az]) /ig, function (z, b) { return b.toUpperCase() });
};
